package com.yc.home.cloudother.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.yc.entity.Doquestion;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author lzk
 * @since 2024-09-23
 */
public interface DoquestionMapper extends BaseMapper<Doquestion> {

    @Select("SELECT DATE_SUB(CURDATE(), INTERVAL days.day_num DAY) AS date,\n" +
            "COALESCE(dq.question_count, 0) AS a  \n" +
            "FROM (   \n" +
            "SELECT 0 AS day_num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6      \n" +
            ") AS days     \n" +
            "LEFT JOIN (\n" +
            "SELECT     \n" +
            "COUNT(*) AS question_count,    \n" +
            "DATE(createtime) AS date     \n" +
            "FROM doquestion     \n" +
            "WHERE createtime >= CURDATE() - INTERVAL 7 DAY AND createtime < CURDATE() + INTERVAL 1 DAY and uid = #{id} \n" +
            "GROUP BY DATE(createtime)  \n" +
            ") AS dq ON DATE_SUB(CURDATE(), INTERVAL days.day_num DAY) = dq.date      \n" +
            "ORDER BY days.day_num DESC")
    List<Map<String, String>> getweeknum(String id);

    @Select("SELECT       \n" +
            "    DATE_SUB(CURDATE(), INTERVAL days.day_num DAY) AS date,\n" +
            "    COALESCE(dq.question_count, 0) AS a    \n" +
            "FROM (      \n" +
            "    SELECT 0 AS day_num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6      \n" +
            ") AS days      \n" +
            "LEFT JOIN (      \n" +
            "    SELECT       \n" +
            "        COUNT(*) AS question_count,    \n" +
            "        DATE(createtime) AS date      \n" +
            "    FROM doquestion      \n" +
            "    WHERE createtime >= CURDATE() - INTERVAL 7 DAY AND createtime < CURDATE() + INTERVAL 1 DAY \n" +
            "    GROUP BY DATE(createtime)   \n" +
            ") AS dq ON DATE_SUB(CURDATE(), INTERVAL days.day_num DAY) = dq.date      \n" +
            "ORDER BY days.day_num DESC;")
    List<Map<String, String>> getallweeknum();

    @Select("SELECT q.*, COUNT(*) AS count  \n" +
            "FROM question q  \n" +
            "JOIN doquestion s ON q.id = s.qid  \n" +
            "WHERE DATE(s.createtime) = CURDATE()\n" +
            "GROUP BY q.id, q.content \n" +
            "ORDER BY count DESC  \n" +
            "LIMIT 0,5")
    List<Map<String, String>> getfive();

    @Select("SELECT q.*, COUNT(*) AS count\n" +
            "from question q\n" +
            "JOIN doquestion s ON q.id = s.qid\n" +
            "where q.status = 1\n" +
            "GROUP BY q.id, q.content\n" +
            "ORDER BY count DESC\n" +
            "limit 0,5")
    List<Map<String, String>> getallfive();

    @Select("select sum(sum) from doquestion")
    int getNum();

    @Select("select money.a,e.b from money left join (\n" +
            "SELECT MONTH(createtime) AS c,\n" +
            "\t\t\t\tSUM(sum) AS b\n" +
            "\t\t\t\tFROM\n" +
            "\t\t\t\tdoquestion\n" +
            "\t\t\t\tWHERE\n" +
            "\t\t\t\tYEAR(createtime) = #{year}\n" +
            "\t\t\t\tAND status <> 7\n" +
            "\t\t\t\tGROUP BY MONTH(createtime) ORDER BY c) as e on money.a=e.c")
    List<Map<String, String>> getmonth(String year);

    @Select("select year.a, d.b from year left join(SELECT\n" +
            "\t\t\t\tYEAR(createtime) AS c,\n" +
            "\t\t\t\tsum(sum) AS b\n" +
            "\t\t\t\tFROM\n" +
            "\t\t\t\tdoquestion\n" +
            "\t\t\t\tWHERE YEAR(createtime) GROUP BY\n" +
            "\t\t\t\tYEAR(createtime)\n" +
            "\t\t\t\tORDER BY c) as d on year.a = d.c\n" +
            "\t\t\t\tGROUP BY year.a,d.b")
    List<Map<String, String>> getyear();

    @Select("select jd.a, d.b from jd left join(SELECT\n" +
            "\t\t\t\tQUARTER(createtime) AS c,\n" +
            "\t\t\t\tSUM(sum) AS b\n" +
            "\t\t\t\tFROM\n" +
            "\t\t\t\tdoquestion\n" +
            "\t\t\t\tWHERE\n" +
            "\t\t\t\tYEAR(createtime) = #{year}\n" +
            "\t\t\t\tGROUP BY\n" +
            "\t\t\t\tQUARTER(createtime)\n" +
            "\t\t\t\tORDER BY c) as d on jd.a = d.c\n" +
            "\t\t\t\tGROUP BY jd.a,d.b")
    List<Map<String, String>> getjd(String year);

    @Select("select content, count from question right join (select qid, sum(sum) as count from doquestion GROUP BY qid ORDER BY count desc limit 0,7) as a on a.qid = question.id")
    List<Map<String, String>> getsevenproduct();

}
